IDE: IntellJ IDEA (可快速生成 Markdown)
第一題
SELECT 學生.s_id AS 編號, 學生.s_name AS 姓名, 學生.s_birth AS 生日, 學生.s_sex AS 性別, 成績表01.s_score AS 課程01分數, 成績表02.s_score AS 課程02分數
FROM Student 學生
JOIN Score 成績表01
ON 學生.s_id = 成績表01.s_id AND 成績表01.c_id = 01
JOIN Score 成績表02
ON 學生.s_id = 成績表02.s_id AND 成績表02.c_id = 02
WHERE 成績表01.s_score > 成績表02.s_score;
編號 | 姓名 | 生日 | 性別 | 課程01分數 | 課程02分數 |
---|---|---|---|---|---|
02 | 錢電 | 1990-12-21 | 男 | 70 | 60 |
04 | 李雲 | 1990-08-06 | 男 | 50 | 30 |
第二題
SELECT 學生.s_id AS 編號, 學生.s_name AS 姓名, 學生.s_birth AS 生日, 學生.s_sex AS 性別, 成績表01.s_score AS 課程01分數, 成績表02.s_score AS 課程02分數
FROM Student 學生
JOIN Score 成績表01
ON 學生.s_id = 成績表01.s_id AND 成績表01.c_id = 01
JOIN Score 成績表02
ON 學生.s_id = 成績表02.s_id AND 成績表02.c_id = 02
WHERE 成績表01.s_score < 成績表02.s_score;
編號 | 姓名 | 生日 | 性別 | 課程01分數 | 課程02分數 |
---|---|---|---|---|---|
01 | 趙雷 | 1990-01-01 | 男 | 80 | 90 |
05 | 周梅 | 1991-12-01 | 女 | 76 | 87 |
第三題
SELECT 學生.s_id AS 編號, 學生.s_name AS 姓名, ROUND(AVG(成績表.s_score), 2) as 平均成績
FROM Student 學生
JOIN Score 成績表
ON 學生.s_id = 成績表.s_id
GROUP BY 學生.s_id
HAVING 平均成績 > 60;
編號 | 姓名 | 平均成績 |
---|---|---|
01 | 趙雷 | 89.67 |
02 | 錢電 | 70.00 |
03 | 孫風 | 80.00 |
05 | 周梅 | 81.50 |
07 | 鄭竹 | 93.50 |
附加題
SELECT 學生.s_id AS 編號, 學生.s_name AS 姓名, SUM(成績表.s_score) as 總分
FROM Student 學生
JOIN Score 成績表
ON 學生.s_id = 成績表.s_id
GROUP BY 學生.s_id
HAVING 總分 > 200;
編號 | 姓名 | 總分 |
---|---|---|
01 | 趙雷 | 269 |
02 | 錢電 | 210 |
03 | 孫風 | 240 |
第四題
SELECT 學生.s_id AS 編號, 學生.s_name AS 姓名, ROUND(AVG(成績表.s_score), 2) as 平均成績
FROM Student 學生
LEFT JOIN Score 成績表 # 用 LEFT JOIN 才能取出平均成績為 NULL 的學生
ON 學生.s_id = 成績表.s_id
GROUP BY 學生.s_id
HAVING 平均成績 < 60 OR 平均成績 IS NULL;
編號 | 姓名 | 平均成績 |
---|---|---|
04 | 李雲 | 33.33 |
06 | 吳蘭 | 32.50 |
08 | 王菊 | null |
第五題
SELECT 學生.s_id AS 編號, 學生.s_name AS 姓名, COUNT(成績表.c_id) AS 選課總數, SUM(成績表.s_score) AS 總成績
FROM Student 學生
LEFT JOIN Score 成績表
ON 學生.s_id = 成績表.s_id
GROUP BY 學生.s_id;
編號 | 姓名 | 選課總數 | 總成績 |
---|---|---|---|
01 | 趙雷 | 3 | 269 |
02 | 錢電 | 3 | 210 |
03 | 孫風 | 3 | 240 |
04 | 李雲 | 3 | 100 |
05 | 周梅 | 2 | 163 |
06 | 吳蘭 | 2 | 65 |
07 | 鄭竹 | 2 | 187 |
08 | 王菊 | 0 | null |
第六題
SELECT COUNT(老師.t_name) AS 個數
FROM Teacher 老師
WHERE 老師.t_name LIKE "李%";
個數 |
---|
1 |
第七題
因為張三老師教數學,這題換句話說,也就是在問哪些同學有修過數學,數學的 c_id 是 02,如果張三教多堂課就不適用了!
SELECT 學生.s_id AS 編號, 學生.s_name AS 姓名, 學生.s_birth AS 生日, 學生.s_sex AS 性別
FROM Student 學生
JOIN Score 成績表
ON 學生.s_id = 成績表.s_id
WHERE 成績表.c_id = 02;
編號 | 姓名 | 生日 | 性別 |
---|---|---|---|
01 | 趙雷 | 1990-01-01 | 男 |
02 | 錢電 | 1990-12-21 | 男 |
03 | 孫風 | 1990-05-20 | 男 |
04 | 李雲 | 1990-08-06 | 男 |
05 | 周梅 | 1991-12-01 | 女 |
07 | 鄭竹 | 1989-07-01 | 女 |
第八題
SELECT 學生.s_id AS 編號, 學生.s_name AS 姓名, 學生.s_birth AS 生日, 學生.s_sex AS 性別
FROM Student 學生
WHERE 學生.s_id NOT IN (
SELECT 成績表.s_id
FROM Score 成績表
JOIN Course 課程
ON 成績表.c_id = 課程.c_id
WHERE 課程.t_id = (
SELECT 老師.t_id
FROM Teacher 老師
WHERE 老師.t_name LIKE "張%"
));
編號 | 姓名 | 生日 | 性別 |
---|---|---|---|
06 | 吳蘭 | 1992-03-01 | 女 |
08 | 王菊 | 1990-01-20 | 女 |
第九題
SELECT 學生.s_id AS 編號, 學生.s_name AS 姓名, 學生.s_birth AS 生日, 學生.s_sex AS 性別
FROM Student 學生
JOIN Score 成績表01
ON 學生.s_id = 成績表01.s_id AND 成績表01.c_id = 01
JOIN Score 成績表02
ON 學生.s_id = 成績表02.s_id AND 成績表02.c_id = 02
編號 | 姓名 | 生日 | 性別 |
---|---|---|---|
01 | 趙雷 | 1990-01-01 | 男 |
02 | 錢電 | 1990-12-21 | 男 |
03 | 孫風 | 1990-05-20 | 男 |
04 | 李雲 | 1990-08-06 | 男 |
05 | 周梅 | 1991-12-01 | 女 |
第十題
MySQL 沒有差集語法 (╯•̀ὤ•́)╯
SELECT 學生.s_id AS 編號, 學生.s_name AS 姓名, 學生.s_birth AS 生日, 學生.s_sex AS 性別
FROM Student 學生, Score 成績表01
WHERE 學生.s_id = 成績表01.s_id AND 成績表01.c_id = 01 AND 成績表01.s_id NOT IN (
SELECT 成績表02.s_id
FROM Student 學生, Score 成績表02
WHERE 學生.s_id = 成績表02.s_id AND 成績表02.c_id = 02);
編號 | 姓名 | 生日 | 性別 |
---|---|---|---|
06 | 吳蘭 | 1992-03-01 | 女 |